package com.beiwang2;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.provider.Settings;
import android.util.Log;

import androidx.annotation.NonNull;

import com.facebook.react.bridge.ReadableArray;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

public class DBManager {
    private static final String TAG = "BeiwangDB";
    private DBHelper dbHelper;
    public DBManager(Context context) {
        this.dbHelper = new DBHelper(context);
    }
    SQLiteDatabase db = null;

    /*查询重要事务[Imptask表]*/
    public ArrayList<Map<String, String>> selectImptask(){
        String sql = "select rowid, * from Imptask order by checked asc";
        ArrayList<Map<String, String>> list = new ArrayList<>();
        try {
            db = dbHelper.getWritableDatabase();
            Cursor cursor = db.rawQuery(sql,null);
            String[] strArray = {"rowid","title","task","datetime","impcode","checked","alarmid"};
            while(cursor.moveToNext()){
                Map<String, String> map = new HashMap<>();
                for (int i = 0; i < strArray.length; i++){
                    map.put(strArray[i], cursor.getString(i));
                }
                list.add(map);
            }
            cursor.close();
        }catch (Exception e) {
            Log.e(TAG, "selectImptask error", e);
        } finally {
            if (db != null) {
                db.close();
            }
        }
        return list;
    }

    /*查询重要事务[Imptask表]*/
    public ArrayList<Map<String, String>> sortImptask(String type){
        String sql = "select rowid, * from Imptask order by impcode "+ type +",checked asc";
        ArrayList<Map<String, String>> list = new ArrayList<>();
        try {
            db = dbHelper.getWritableDatabase();
            Cursor cursor = db.rawQuery(sql,null);
            String[] strArray = {"rowid","title","task","datetime","impcode","checked","alarmid"};
            while(cursor.moveToNext()){
                Map<String, String> map = new HashMap<>();
                for (int i = 0; i < strArray.length; i++){
                    map.put(strArray[i], cursor.getString(i));
                }
                list.add(map);
            }
            cursor.close();
        }catch (Exception e) {
            Log.e(TAG, "sortImptask error", e);
        } finally {
            if (db != null) {
                db.close();
            }
        }
        return list;
    }

    /*保存重要事务[Imptask表]*/
    public boolean saveImptask(String title, String task, String datetime, String impcode,int alarmid) {
        try {
            db = dbHelper.getWritableDatabase();
            ContentValues cv = new ContentValues();
            cv.put("title", title);
            cv.put("task", task);
            cv.put("datetime", datetime);
            cv.put("alarmid", alarmid);
            cv.put("impcode", impcode);
            db.insert(DBHelper.IMPTASK_TABLE, null, cv);
            return true;
        } catch (Exception e) {
            Log.e(TAG, "saveImpTask error", e);
            return false;
        } finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*修改重要事务状态[Imptask表]*/
    public void isCheckImptask(int rowid){
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "update Imptask set checked = case checked when 0 then 1 when 1 then 0 end where rowid=?";
            db.execSQL(sql,new Object[]{rowid});
        }catch (Exception e){
            Log.e(TAG, "isCheckImptask error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*删除重要事务可批量删除*/
    public void delImptask(ReadableArray rowids){
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "delete from Imptask where rowid=?";
            for (int i = 0;i<rowids.size();i++){
                db.execSQL(sql,new Object[]{rowids.getInt(i)});
            }
        }catch (Exception e){
            Log.e(TAG, "delImptask error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*重要事务详情查询*/
    public Map<String, String> taskdetails(int rowid){
        Map<String, String> details = new HashMap<>();
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "select rowid, * from Imptask where rowid=?";
            Cursor cursor = db.rawQuery(sql,new String[]{String.valueOf(rowid)});
            String[] strArray = {"rowid","title","task","datetime","impcode","checked","alarmid"};
            if (cursor.moveToFirst())  {
                for (int i = 0; i < strArray.length; i++){
                    details.put(strArray[i], cursor.getString(i));
                }
            }
            cursor.close();
        }catch (Exception e){
            Log.e(TAG, "taskdetails error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return details;
    }

    /*编辑重要事务详情查询*/
    public boolean editTask(int rowid, String title, String task, String datetime, String impcode,int alarmid){
        String sql = "update Imptask set title=?, task=?, datetime=?, impcode=? ,alarmid=? where rowid=?";
        try {
            db = dbHelper.getWritableDatabase();
            db.execSQL(sql,new Object[]{title,task,datetime,impcode,alarmid,rowid});
            return true;
        }catch (Exception e){
            Log.e(TAG, "editTask error", e);
            return false;
        }finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*添加计划*/
    public boolean addPlan(String title,String detail,ReadableArray imgUri){
        try {
            db = dbHelper.getWritableDatabase();
            ContentValues cv = new ContentValues();
            cv.put("title", title);
            cv.put("detail", detail);
            if(imgUri.size() > 0){
                for (int i = 0; i < imgUri.size(); i++) {
                    String uri = imgUri.getString(i);
                    cv.put("img"+(i+1)+"uri",uri);
                    File file = new File(uri.substring(8));
                    InputStream inputStream = null;
                    try {
                        ByteArrayOutputStream baos = new ByteArrayOutputStream();
                        inputStream = new FileInputStream(file);
                        byte[] images = readResource(inputStream);
                        Bitmap bitmap=BitmapFactory.decodeByteArray(images, 0, images.length);
                        bitmap.compress(Bitmap.CompressFormat.PNG, 100, baos);
                        cv.put("img"+(i+1), baos.toByteArray());
                        baos.close();
                    }catch (FileNotFoundException e) {
                        e.printStackTrace();
                    }finally {
                        assert inputStream != null;
                        inputStream.close();
                    }
                }
            }
            db.insert(DBHelper.PLANTASK_TABLE, null, cv);
            return true;
        }catch (Exception e){
            Log.e(TAG, "addPlan error", e);
        }finally {
            db.close();
        }
        return false;
    }

    /*查询计划*/
    public ArrayList<Map<String, String>> selectPlan(int state,String str){
        db = dbHelper.getWritableDatabase();
        ArrayList<Map<String, String>> list = new ArrayList<>();
        try {
            if(state == 0 || state == 1){
                String sql = "select planid,title,detail,img1uri,img2uri from Plantask where state=?";
                Cursor cursor = db.rawQuery(sql,new String[]{String.valueOf(state)});
                String[] strArray = {"planid","title","detail","img1uri","img2uri"};
                while(cursor.moveToNext()){
                    Map<String, String> map = new HashMap<>();
                    for (int i = 0; i < strArray.length; i++){
                        map.put(strArray[i], cursor.getString(i));
                    }
                    list.add(map);
                }
                cursor.close();
            }
            if(state == 2){
                String sql = "select planid,title,startTime,plantime,planworktime,actuallyworktime from "+
                        DBHelper.PLANTASK_TABLE +" where state=? order by plantime asc ";
                Cursor cursor = db.rawQuery(sql,new String[]{String.valueOf(state)});
                String[] strArray = {"planid","title","startTime","planTime","planWorkTime","actuallyWorkTime"};
                while(cursor.moveToNext()){
                    Map<String, String> map = new HashMap<>();
                    for (int i = 0; i < strArray.length; i++){
                        map.put(strArray[i], cursor.getString(i));
                    }
                    list.add(map);
                }
                cursor.close();
            }
            if(state == 3 || state == 4){
                String sql = "select planid,title,startTime,plantime,planworktime,actuallyworktime,endtime from "+
                        DBHelper.PLANTASK_TABLE +" where state=? and title like '%"+str+"%'";
                Cursor cursor = db.rawQuery(sql,new String[]{String.valueOf(state)});
                String[] strArray = {"planid","title","startTime","planTime","planWorkTime","actuallyWorkTime","endTime"};
                while(cursor.moveToNext()){
                    Map<String, String> map = new HashMap<>();
                    for (int i = 0; i < strArray.length; i++){
                        map.put(strArray[i], cursor.getString(i));
                    }
                    list.add(map);
                }
                cursor.close();
            }
        }catch (Exception e){
            Log.e(TAG, "addPlan error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return list;
    }

    /*计划详情*/
    public Map<String, String> planDetail(int planId){
        Map<String, String> details = new HashMap<>();
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "select planid,title,detail,img1uri,img2uri from " +DBHelper.PLANTASK_TABLE +" where planid=?";
            Cursor cursor = db.rawQuery(sql,new String[]{String.valueOf(planId)});
            String[] strArray = {"planid","title","detail","img1uri","img2uri"};
            if (cursor.moveToFirst())  {
                for (int i = 0; i < strArray.length; i++){
                    details.put(strArray[i], cursor.getString(i));
                }
            }
            cursor.close();
        }catch (Exception e){
            Log.e(TAG, "planDetail error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return details;
    }

    /*删除计划*/
    public boolean delPlan(int planid){
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "delete from Plantask where rowid=?";
                db.execSQL(sql,new Object[]{planid});
            return true;
        }catch (Exception e){
            Log.e(TAG, "delImptask error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return false;
    }

    /*编辑计划*/
    public boolean editPlan(int planId, String title, String detail,ReadableArray imgUri){
        String sql = "update Plantask set title=?, detail=? ,img1=? ,img2=? ,img1uri=? ,img2uri=?  where planid=?";
        try {
            db = dbHelper.getWritableDatabase();
            String img1uri = null;
            String img2uri = null;
            byte[] img1 = new byte[0];
            byte[] img2 = new byte[0];
            if(imgUri.size() > 0){
                for (int i = 0; i < imgUri.size(); i++) {
                    String uri = imgUri.getString(i);
                    File file = new File(uri.substring(8));
                    InputStream inputStream = null;
                    try {
                        ByteArrayOutputStream baos = new ByteArrayOutputStream();
                        inputStream = new FileInputStream(file);
                        byte[] images = readResource(inputStream);
                        Bitmap bitmap=BitmapFactory.decodeByteArray(images, 0, images.length);
                        bitmap.compress(Bitmap.CompressFormat.PNG, 100, baos);
                        if(i==0){
                            img1uri = uri;
                            img1 = baos.toByteArray();
                        }
                        if (i==1){
                            img2uri=  uri;
                            img2 = baos.toByteArray();
                        }
                        baos.close();
                    }catch (FileNotFoundException e) {
                        e.printStackTrace();
                    }finally {
                        assert inputStream != null;
                        inputStream.close();
                    }
                }
            }
            db.execSQL(sql,new Object[]{title,detail,img1,img2,img1uri,img2uri,planId});
            return true;
        }catch (Exception e){
            Log.e(TAG, "editTask error", e);
            return false;
        }finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*改变计划状态*/
    public boolean changePlanState(int planId,int state){
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "update Plantask set state = ? where planid=?";
            db.execSQL(sql,new Object[]{state,planId});
            if(state==4){
                String sql2 = " update " + DBHelper.USER_TABLE+" set complete=complete+1";
                db.execSQL(sql2);
            }
            return true;
        }catch (Exception e){
            Log.e(TAG, "changePlanState error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return false;
    }

    /*开始事务*/
    public boolean startPlan(int planId, String startTime, String planTime, double planWorkTime){
        try {
            db = dbHelper.getWritableDatabase();
            String sql = ("update " + DBHelper.PLANTASK_TABLE + " set startTime=?,planTime=?,planworktime=?,state=2 where planid=?");
            db.execSQL(sql,new Object[]{startTime,planTime,planWorkTime,planId});
            return true;
        }catch (Exception e){
            Log.e(TAG, "startPlan error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return  false;
    }

    /*更新工作时长*/
    public boolean updateWorkTime(int planId,double workTime){
        try {
            db = dbHelper.getWritableDatabase();
            String sql = ("update " + DBHelper.PLANTASK_TABLE + " set actuallyworktime=actuallyworktime+? where planid=?");
            db.execSQL(sql,new Object[]{workTime,planId});
            @SuppressLint("SimpleDateFormat") SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            String sql2 = ("update " + DBHelper.USER_TABLE + " set worktime=worktime+?");
            db.execSQL(sql2,new Object[]{workTime});
            updateDaily(df.format(new Date()),workTime);
            return true;
        }catch (Exception e){
            Log.e(TAG, "updateWorkTime error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return  false;
    }

    /*更新结束时间*/
    public boolean updateEndTime(int planId,String endTime){
        @SuppressLint("SimpleDateFormat") SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        try {
            db = dbHelper.getWritableDatabase();
            String sql = ("update " + DBHelper.PLANTASK_TABLE + " set endtime=? where planid=?");
            db.execSQL(sql,new Object[]{endTime,planId});
            String sql3 =("select plantime,planworktime,actuallyworktime from " +
                    DBHelper.PLANTASK_TABLE+ " where planid=?");
            String sql4 = ("update " + DBHelper.USER_TABLE +" set timeout=timeout+1");
            Cursor cursor = db.rawQuery(sql3,new String[]{String.valueOf(planId)});
            cursor.moveToFirst();
            Date date1 = df.parse(cursor.getString(0));
            Date date2 = df.parse(endTime);
            if(date1.compareTo(date2) < 0){
                db.execSQL(sql4);
            }
            double time1 = Double.parseDouble(cursor.getString(1));
            double time2 = Double.parseDouble(cursor.getString(2));
            if(time1<time2){
                db.execSQL(sql4);
            }
            cursor.close();
            return true;
        }catch (Exception e){
            Log.e(TAG, "updateEndTime error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return false;
    }

    /*计划完成详情*/
    public Map<String, String> compDetail(int planId){
        Map<String, String> details = new HashMap<>();
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "select planid,title,detail,img1uri,img2uri,startTime,plantime,planworktime,actuallyworktime,endtime,note from "
                    +DBHelper.PLANTASK_TABLE +" where planid=?";
            Cursor cursor = db.rawQuery(sql,new String[]{String.valueOf(planId)});
            String[] strArray = {"planid","title","detail","img1uri",
                    "img2uri","startTime","planTime","planWorkTime","actuallyWorkTime","endTime","note"};
            if (cursor.moveToFirst())  {
                for (int i = 0; i < strArray.length; i++){
                    details.put(strArray[i], cursor.getString(i));
                }
            }
            cursor.close();
        }catch (Exception e){
            Log.e(TAG, "planDetail error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return details;
    }

    /*创建笔记本*/
    public boolean createNoteBook(String name,String coveruri,ReadableArray tag) {
        try {
            db = dbHelper.getWritableDatabase();
            ContentValues cv = new ContentValues();
            cv.put("coveruri",coveruri);
            @SuppressLint("SimpleDateFormat") SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            cv.put("name",name);
            cv.put("creattime",df.format(new Date()));
            cv.put("tag",tag.toString());
            db.insert(DBHelper.NOTEBOOK_TABLE, null, cv);
            return true;
        }catch (Exception e) {
            Log.e(TAG, "addPlan error", e);
        }finally {
            if(db != null){
                db.close();
            }
        }
        return false;
    }

    /*得到笔记本*/
    public ArrayList<Map<String, String>> selectNoteBook(String str){
        ArrayList<Map<String, String>> list = new ArrayList<>();
        String sql = "select bookid,name,coveruri,tag,creattime from "
                + DBHelper.NOTEBOOK_TABLE + " where name like '%"+str+"%' or tag like '%"+str+"%' order by creattime desc";
        try {
            db = dbHelper.getWritableDatabase();
            Cursor cursor = db.rawQuery(sql,null);
            String[] strArray = {"bookid","name","coveruri","tag","creattime"};
            while(cursor.moveToNext()){
                Map<String, String> map = new HashMap<>();
                for (int i = 0; i < strArray.length; i++){
                    map.put(strArray[i], cursor.getString(i));
                }
                list.add(map);
            }
            cursor.close();
        }catch (Exception e) {
            Log.e(TAG, "selectNoteBook error", e);
        }finally {
            if(db != null){
                db.close();
            }
        }
        return list;
    }

    /*创建笔记表*/
    public void createNoteTable(int bookid){
        db = dbHelper.getWritableDatabase();
        String sql = "create table if not exists " + "notetable_father_is_"+bookid +
                "( noteid integer primary key AUTOINCREMENT,"+
                "title text , detail text, " +
                "creattime text, " +
                "voice blob,"+
                "voiceurl text,"+
                "imguri text,"+
                "isdelete int default 0" +
                ") ";
        db.execSQL(sql);
    }

    /*编辑笔记本*/
    public boolean editNoteBook(int bookid,String name,String coveruri,ReadableArray tag){
        String sql = "update "+DBHelper.NOTEBOOK_TABLE +" set name=?, coveruri=?,tag=? where bookid=?";
        try {
            db = dbHelper.getWritableDatabase();
            db.execSQL(sql,new Object[]{name,coveruri,tag.toString(),bookid});
            return true;
        }catch (Exception e){
            Log.e(TAG, "editNoteBook error", e);
            return false;
        }finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*获得笔记本详情*/
    public Map<String, String> noteBookDetail(int bookid){
        Map<String, String> details = new HashMap<>();
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "select bookid,name,coveruri,tag from " +DBHelper.NOTEBOOK_TABLE +" where bookid=?";
            Cursor cursor = db.rawQuery(sql,new String[]{String.valueOf(bookid)});
            String[] strArray = {"bookid","name","coveruri","tag"};
            if (cursor.moveToFirst())  {
                for (int i = 0; i < strArray.length; i++){
                    details.put(strArray[i], cursor.getString(i));
                }
            }
            cursor.close();
        }catch (Exception e){
            Log.e(TAG, "noteBookDetail error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return details;
    }

    /*删除笔记本*/
    public boolean delNoteBook(int bookid){
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "delete from "+DBHelper.NOTEBOOK_TABLE +" where bookid=?";
            String sql2 = "drop table IF EXISTS " + "notetable_father_is_"+bookid;
            db.execSQL(sql,new Object[]{bookid});
            db.execSQL(sql2);
            return true;
        }catch (Exception e){
            Log.e(TAG, "delNoteBook error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return false;
    }

    /*添加笔记*/
    public boolean saveNote(String title, String detail, ReadableArray imgUri,String voiceurl,int bookid) {
        try {
            db = dbHelper.getWritableDatabase();
            @SuppressLint("SimpleDateFormat") SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            ContentValues cv = new ContentValues();
            cv.put("title", title);
            cv.put("detail", detail);
            cv.put("creattime", df.format(new Date()));
            cv.put("voiceurl", voiceurl);
            cv.put("imguri", imgUri.toString());
            db.insert("notetable_father_is_"+bookid, null, cv);
            return true;
        } catch (Exception e) {
            Log.e(TAG, "saveNote error", e);
            return false;
        } finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*编辑笔记*/
    public boolean editNote(String title, String detail, ReadableArray imgUri,String voiceurl,int noteid,int bookid){
        @SuppressLint("SimpleDateFormat") SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String sql = "update "+"notetable_father_is_"+bookid
                +" set title=?, detail=?,creattime=?,imguri=?,voiceurl=? where noteid=?";
        try {
            db = dbHelper.getWritableDatabase();
            db.execSQL(sql,new Object[]{title,detail,df.format(new Date()),imgUri,voiceurl,noteid});
            return true;
        }catch (Exception e){
            Log.e(TAG, "editNote error", e);
            return false;
        }finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*还原笔记*/
    public boolean restoreNote(int id ,int noteid,int bookid){
        String sql = "update "+"notetable_father_is_"+bookid
                +" set isdelete=0 where noteid=?";
        String sql2 = " delete from " + DBHelper.RECYCLEBIN_TABLE + " where id=? ";
        try {
            db = dbHelper.getWritableDatabase();
            db.execSQL(sql,new Object[]{noteid});
            db.execSQL(sql2,new Object[]{id});
            return true;
        }catch (Exception e) {
            Log.e(TAG, "restoreNote", e);
            return false;
        } finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*移动到回收站*/
    public boolean moveToRecycle(int noteid,int bookid,String title){
        String sql = "update "+"notetable_father_is_"+bookid
                +" set isdelete=1 where noteid=?";
        try {
            db = dbHelper.getWritableDatabase();
            @SuppressLint("SimpleDateFormat") SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            ContentValues cv = new ContentValues();
            cv.put("bookid", bookid);
            cv.put("noteid", noteid);
            cv.put("title", title);
            cv.put("time", df.format(new Date()));
            db.insert(DBHelper.RECYCLEBIN_TABLE, null, cv);
            db.execSQL(sql,new Object[]{noteid});
            return true;
        } catch (Exception e) {
            Log.e(TAG, "moveToRecycle", e);
            return false;
        } finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*删除笔记*/
    public boolean delNote(int noteid,int bookid){
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "delete from " + "notetable_father_is_"+bookid+" where noteid=? ";
            db.execSQL(sql,new Object[]{noteid});
            return true;
        }catch (Exception e){
            Log.e(TAG, "delNote error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return false;
    }

    /*获得笔记*/
    public ArrayList<Map<String, String>> selectNotes(int bookid){
        ArrayList<Map<String, String>> list = new ArrayList<>();
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "select noteid,title,detail,creattime from " +
                    "notetable_father_is_"+bookid+" where isdelete=0";
            Cursor cursor = db.rawQuery(sql,null);
            String[] strArray = {"noteid","title","detail","creattime"};
            while(cursor.moveToNext()){
                Map<String, String> map = new HashMap<>();
                for (int i = 0; i < strArray.length; i++){
                    map.put(strArray[i], cursor.getString(i));
                }
                list.add(map);
            }
            cursor.close();
        }catch (Exception e){
            Log.e(TAG, "noteBookDetail error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return list;
    }

    public void delNoteBin(int id){
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "delete from " + DBHelper.RECYCLEBIN_TABLE +" where id=? ";
            db.execSQL(sql,new Object[]{id});
        }catch (Exception e){
            Log.e(TAG, "delNoteBin error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*获得笔记详情*/
    public Map<String, String> noteDetail(int noteid,int bookid){
        Map<String, String> details = new HashMap<>();
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "select title,detail,creattime,voiceurl,imguri from " +"notetable_father_is_"+bookid+" where noteid=?";
            Cursor cursor = db.rawQuery(sql,new String[]{String.valueOf(noteid)});
            String[] strArray = {"title","detail","creattime","voiceurl","imguri"};
            if (cursor.moveToFirst())  {
                for (int i = 0; i < strArray.length; i++){
                    details.put(strArray[i], cursor.getString(i));
                }
            }
            cursor.close();
        }catch (Exception e){
            Log.e(TAG, "noteBookDetail error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return details;
    }

    /*搜索笔记*/
    public ArrayList<Map<String, String>> searchNote(String str,int bookid){
        String sql = " select * from " +"notetable_father_is_"+bookid
                +" where title like '%"+str+"%' or detail like '%"+str+"%'";
        ArrayList<Map<String, String>> list = new ArrayList<>();
        try {
            db = dbHelper.getWritableDatabase();
            Cursor cursor = db.rawQuery(sql,null);
            String[] strArray = {"noteid","title","detail","creattime"};
            while(cursor.moveToNext()){
                Map<String, String> map = new HashMap<>();
                for (int i = 0; i < strArray.length; i++){
                    map.put(strArray[i], cursor.getString(i));
                }
                list.add(map);
            }
            cursor.close();
        }catch (Exception e){
            Log.e(TAG, "searchNote error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return list;
    }

    /*回收笔记*/
    public ArrayList<Map<String, String>> selectNoteBin(){
        ArrayList<Map<String, String>> list = new ArrayList<>();
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "select * from " + DBHelper.RECYCLEBIN_TABLE;
            Cursor cursor = db.rawQuery(sql,null);
            String[] strArray = {"id","bookid","noteid","title","time"};
            while(cursor.moveToNext()){
                Map<String, String> map = new HashMap<>();
                for (int i = 0; i < strArray.length; i++){
                    map.put(strArray[i], cursor.getString(i));
                }
                list.add(map);
            }
            cursor.close();
        }catch (Exception e){
            Log.e(TAG, "selectNoteBin error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return list;
    }

    /*添加验收报告*/
    public boolean saveReport(int planid,String title,String report,ReadableArray fileurls,String complete_time){
        try {
            db = dbHelper.getWritableDatabase();
            @SuppressLint("SimpleDateFormat") SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            ContentValues cv = new ContentValues();
            cv.put("planid", planid);
            cv.put("title", title);
            cv.put("report", report);
            cv.put("fileurls", fileurls.toString());
            cv.put("time", df.format(new Date()));
            cv.put("complete_time", complete_time);
            db.insert(DBHelper.REPORT_TABLE, null, cv);
            return true;
        } catch (Exception e) {
            Log.e(TAG, "saveReport", e);
            return false;
        } finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*获得验收报告*/
    public ArrayList<Map<String, String>> selectAccReport(){
        ArrayList<Map<String, String>> list = new ArrayList<>();
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "select id,planid,title,report,time from " + DBHelper.REPORT_TABLE +
                    " where isdelete=0";
            Cursor cursor = db.rawQuery(sql,null);
            String[] strArray = {"id","planid","title","report","time"};
            while(cursor.moveToNext()){
                Map<String, String> map = new HashMap<>();
                for (int i = 0; i < strArray.length; i++){
                    map.put(strArray[i], cursor.getString(i));
                }
                list.add(map);
            }
            cursor.close();
        }catch (Exception e){
            Log.e(TAG, "selectAccReport error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return list;
    }

    /*获得验收数量*/
    public int getReportCount(){
        int count=0;
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "select id from " + DBHelper.REPORT_TABLE +" where isdelete=0";
            Cursor cursor = db.rawQuery(sql,null);
            count = cursor.getCount();
            cursor.close();
        }catch (Exception e){
            Log.e(TAG, "selectAccReport error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return count;
    }

    /*获得验收报告详情*/
    public Map<String, String> accDetail(int id){
        Map<String, String> details = new HashMap<>();
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "select planid,title,report,fileurls,time,complete_time from " +DBHelper.REPORT_TABLE+" where id=?";
            Cursor cursor = db.rawQuery(sql,new String[]{String.valueOf(id)});
            String[] strArray = {"planid","title","report","fileurls","time","complete_time"};
            if (cursor.moveToFirst())  {
                for (int i = 0; i < strArray.length; i++){
                    details.put(strArray[i], cursor.getString(i));
                }
            }
            cursor.close();
        }catch (Exception e){
            Log.e(TAG, "accDetail error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return details;
    }

    /*编辑验收报告*/
    public boolean editReport(int id,String report,ReadableArray fileurls){
        @SuppressLint("SimpleDateFormat") SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String sql = "update "+DBHelper.REPORT_TABLE
                +" set report=?,fileurls=?,time=? where id=?";
        try {
            db = dbHelper.getWritableDatabase();
            db.execSQL(sql,new Object[]{report,fileurls,df.format(new Date()),id});
            return true;
        }catch (Exception e){
            Log.e(TAG, "editReport error", e);
            return false;
        }finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*删除验收报告*/
    public boolean delReport(int id){
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "delete from " + DBHelper.REPORT_TABLE +" where id=? ";
            db.execSQL(sql,new Object[]{id});
            return true;
        }catch (Exception e){
            Log.e(TAG, "delReport error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return false;
    }

    /*添加日志*/
    public boolean saveMineLog(
            String workname,String work, String unfinished,String problem,
            ReadableArray imgurls,ReadableArray fileurls,String time){
        try {
            db = dbHelper.getWritableDatabase();
            ContentValues cv = new ContentValues();
            cv.put("workname", workname);
            cv.put("work", work);
            cv.put("unfinished", unfinished);
            cv.put("problem", problem);
            cv.put("imgurls", imgurls.toString());
            cv.put("fileurls", fileurls.toString());
            cv.put("time", time);
            db.insert(DBHelper.MINELOG_TABLE, null, cv);
            return true;
        } catch (Exception e) {
            Log.e(TAG, "saveMineLog", e);
            return false;
        } finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*获得日志*/
    public ArrayList<Map<String, String>> selectMineLog(){
        ArrayList<Map<String, String>> list = new ArrayList<>();
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "select * from " + DBHelper.MINELOG_TABLE +
                    " where isdelete=0";
            Cursor cursor = db.rawQuery(sql,null);
            String[] strArray = {"id","workname","work",
                    "unfinished","problem","imgurls","fileurls","time"};
            while(cursor.moveToNext()){
                Map<String, String> map = new HashMap<>();
                for (int i = 0; i < strArray.length; i++){
                    map.put(strArray[i], cursor.getString(i));
                }
                list.add(map);
            }
            cursor.close();
        }catch (Exception e){
            Log.e(TAG, "selectMineLog error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return list;
    }

    /*获得日志基本信息*/
    public Map<String, String> mineLog(){
        Map<String, String> info = new HashMap<>();
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "select time from " +DBHelper.MINELOG_TABLE+" where isdelete=0 order by time desc ";
            Cursor cursor = db.rawQuery(sql,null);
            if(cursor.getCount()!=0) {
                cursor.moveToFirst();
                info.put("logCount", String.valueOf(cursor.getCount()));
                info.put("lastTime", String.valueOf(cursor.getString(0)));
                cursor.close();
            }
        }catch (Exception e){
            Log.e(TAG, "mineLog error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return info;
    }

    /*获得验收报告详情*/
    public Map<String, String> logDetail(int id){
        Map<String, String> details = new HashMap<>();
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "select * from " +DBHelper.MINELOG_TABLE+" where id=?";
            Cursor cursor = db.rawQuery(sql,new String[]{String.valueOf(id)});
            String[] strArray = {"id","workname","work","unfinished","problem","imgurls","fileurls","time"};
            if (cursor.moveToFirst())  {
                for (int i = 0; i < strArray.length; i++){
                    details.put(strArray[i], cursor.getString(i));
                }
            }
            cursor.close();
        }catch (Exception e){
            Log.e(TAG, "logDetail error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return details;
    }

    /*编辑验收报告*/
    public boolean editLog(int id,
                           String workname,String work,String unfinished,
                           String problem,ReadableArray imgurls,
                           ReadableArray fileurls,String time
                           ){
        String sql = "update "+DBHelper.MINELOG_TABLE
                +" set workname=?,work=?,unfinished=?,problem=?,imgurls=?,fileurls=?,time=? where id=?";
        try {
            db = dbHelper.getWritableDatabase();
            db.execSQL(sql,new Object[]{workname,work,unfinished,problem,imgurls,fileurls,time,id});
            return true;
        }catch (Exception e){
            Log.e(TAG, "editReport error", e);
            return false;
        }finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*删除验收报告*/
    public boolean delLog(int id){
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "delete from " + DBHelper.MINELOG_TABLE +" where id=? ";
            db.execSQL(sql,new Object[]{id});
            return true;
        }catch (Exception e){
            Log.e(TAG, "delLog error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return false;
    }

    /*更新每日统计表*/
    public void updateDaily(String date,double time){
        String sql = "select date,worktime from " +DBHelper.DAILY_TABLE+" order by date desc";
        String sql2 = "update " +DBHelper.DAILY_TABLE+" set worktime=worktime+? where date=?";
        try {
            db = dbHelper.getWritableDatabase();
            Cursor cursor = db.rawQuery(sql,null);
            if(cursor.getCount()!=0){
                cursor.moveToFirst();
                if(cursor.getString(0).equals(date)){
                    db.execSQL(sql2,new Object[]{time,date});
                }else{
                    ContentValues cv = new ContentValues();
                    cv.put("date", date);
                    cv.put("worktime", time);
                    db.insert(DBHelper.DAILY_TABLE, null, cv);
                }
            }else{
                ContentValues cv = new ContentValues();
                cv.put("date", date);
                cv.put("worktime", time);
                db.insert(DBHelper.DAILY_TABLE, null, cv);
                cursor.close();
            }
            cursor.close();
        }catch (Exception e){
            Log.e(TAG, "updateUser error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*获得每日统计*/
    public ArrayList<Map<String, String>> selectDaily(String date){
        ArrayList<Map<String, String>> list = new ArrayList<>();
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "select date,worktime from " +
                    DBHelper.DAILY_TABLE +" where date like '%"+date+"%' order by date asc";
            Cursor cursor = db.rawQuery(sql,null);
            String[] strArray = {"date","worktime"};
            while(cursor.moveToNext()){
                Map<String, String> map = new HashMap<>();
                for (int i = 0; i < strArray.length; i++){
                    map.put(strArray[i], cursor.getString(i));
                }
                list.add(map);
            }
            cursor.close();
        }catch (Exception e){
            Log.e(TAG, "selectDaily error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return list;
    }

    /*更新用户总工作时长*/
    public void updateUserWorkTime(double time){
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "update " + DBHelper.USER_TABLE +" set worktime=worktime"+time;
            db.execSQL(sql,null);
        }catch (Exception e){
            Log.e(TAG, "delLog error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*更新用户使用天数*/
    public void updateUserDay(String date){
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "update " + DBHelper.USER_TABLE +" set day=day+1";
            String sql2 = " select today from " +DBHelper.USER_TABLE;
            String sql3 = "update " + DBHelper.USER_TABLE +" set today=?";
            Cursor cursor = db.rawQuery(sql2,null);
            cursor.moveToFirst();
            String today = cursor.getString(0);
            cursor.close();
            if (!today.equals(date)) {
                db.execSQL(sql);
                db.execSQL(sql3,new Object[]{date});
            }
        }catch (Exception e){
            Log.e(TAG, "delLog error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*获得重要事务统计*/
    public ArrayList<Map<String, Object>> impTypeCount(){
        ArrayList<Map<String, Object>> list = new ArrayList<>();
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "select count('impcode') as imp_count from " + DBHelper.IMPTASK_TABLE +" group by impcode ";
            Cursor cursor = db.rawQuery(sql,null);
            String[] strArray = {"一般事务","重要事务","紧急事务"};
            int i = 0;
            while(cursor.moveToNext()){
                Map<String, Object> map = new HashMap<>();
                map.put("x", strArray[i++]);
                map.put("y", cursor.getInt(0));
                list.add(map);
            }
            cursor.close();
        }catch (Exception e){
            Log.e(TAG, "selectDaily error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return list;
    }

    /*更新超时*/
    public void updateTimeout(int planid){
            String sql = " update " + DBHelper.PLANTASK_TABLE+
                    " set  isTimeout=1  where planid =?";
        try {
            db = dbHelper.getWritableDatabase();
            db.execSQL(sql,new Object[]{planid});
        }catch (Exception e){
            Log.e(TAG, "updateUserTimeOut error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*获得用户信息*/
    public Map<String, String> selectUser(){
        Map<String, String> info = new HashMap<>();
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "select worktime,timeout,complete,day,slogan from " + DBHelper.USER_TABLE;
            Cursor cursor = db.rawQuery(sql,null);
            String[] strArray = {"worktime","timeout","complete","day","slogan"};
            if (cursor.moveToFirst())  {
                for (int i = 0; i < strArray.length; i++){
                    info.put(strArray[i], cursor.getString(i));
                }
            }
            cursor.close();
        }catch (Exception e){
            Log.e(TAG, "selectUser error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return info;
    }

    /*查询超时的任务*/
    public ArrayList<Map<String, String>> timeoutTask(){
        ArrayList<Map<String, String>> list = new ArrayList<>();
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "select planid,title,plantime,planworktime,actuallyworktime from "
                    + DBHelper.PLANTASK_TABLE +" where isTimeout=1 and state!=4 ";
            Cursor cursor = db.rawQuery(sql,null);
            String[] strArray = {"planid","title","plantime","planworktime","actuallyworktime"};
            while(cursor.moveToNext()){
                Map<String, String> map = new HashMap<>();
                for (int i = 0; i < strArray.length; i++){
                    map.put(strArray[i], cursor.getString(i));
                }
                list.add(map);
            }
            cursor.close();
        }catch (Exception e){
            Log.e(TAG, "timeoutTask error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return list;
    }

    /*查询紧急的任务*/
    public ArrayList<Map<String, String>> exigency(){
        ArrayList<Map<String, String>> list = new ArrayList<>();
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "select rowid,title from "
                    + DBHelper.IMPTASK_TABLE +" where impcode=1 and checked=0";
            Cursor cursor = db.rawQuery(sql,null);
            String[] strArray = {"rowid","title"};
            while(cursor.moveToNext()){
                Map<String, String> map = new HashMap<>();
                for (int i = 0; i < strArray.length; i++){
                    map.put(strArray[i], cursor.getString(i));
                }
                list.add(map);
            }
            cursor.close();
        }catch (Exception e){
            Log.e(TAG, "exigency error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return list;
    }

    /*移动到报告回收站*/
    public boolean moveToRpBin(int rpid,String title){
        String sql = "update "+DBHelper.REPORT_TABLE+
                " set isdelete=1 where id=?";
        try {
            db = dbHelper.getWritableDatabase();
            @SuppressLint("SimpleDateFormat") SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            ContentValues cv = new ContentValues();
            cv.put("rpid", rpid);
            cv.put("title", title);
            cv.put("time", df.format(new Date()));
            db.insert(DBHelper.REPORTBIN_TABLE, null, cv);
            db.execSQL(sql,new Object[]{rpid});
            return true;
        } catch (Exception e) {
            Log.e(TAG, "moveToRecycle", e);
            return false;
        } finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*移动到报告回收站*/
    public boolean moveToLogBin(int logid,String title){
        String sql = "update "+DBHelper.MINELOG_TABLE+
                " set isdelete=1 where id=?";
        try {
            db = dbHelper.getWritableDatabase();
            @SuppressLint("SimpleDateFormat") SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            ContentValues cv = new ContentValues();
            cv.put("logid", logid);
            cv.put("title", title);
            cv.put("time", df.format(new Date()));
            db.insert(DBHelper.MINELOGBIN_TABLE, null, cv);
            db.execSQL(sql,new Object[]{logid});
            return true;
        } catch (Exception e) {
            Log.e(TAG, "moveToRecycle", e);
            return false;
        } finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*删除回收站报告*/
    public void delRpBin(int id){
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "delete from " + DBHelper.REPORTBIN_TABLE +" where id=? ";
            db.execSQL(sql,new Object[]{id});
        }catch (Exception e){
            Log.e(TAG, "delRpBin error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*删除回收站日志*/
        public void delLogBin(int id){
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "delete from " + DBHelper.MINELOGBIN_TABLE +" where id=? ";
            db.execSQL(sql,new Object[]{id});
        }catch (Exception e){
            Log.e(TAG, "delRpBin error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*回收报告*/
    public ArrayList<Map<String, String>> selectRpBin(){
        ArrayList<Map<String, String>> list = new ArrayList<>();
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "select * from " + DBHelper.REPORTBIN_TABLE;
            Cursor cursor = db.rawQuery(sql,null);
            String[] strArray = {"id","rpid","title","time"};
            while(cursor.moveToNext()){
                Map<String, String> map = new HashMap<>();
                for (int i = 0; i < strArray.length; i++){
                    map.put(strArray[i], cursor.getString(i));
                }
                list.add(map);
            }
            cursor.close();
        }catch (Exception e){
            Log.e(TAG, "selectNoteBin error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return list;
    }

    /*回收报告*/
    public ArrayList<Map<String, String>> selectLogBin(){
        ArrayList<Map<String, String>> list = new ArrayList<>();
        try {
            db = dbHelper.getWritableDatabase();
            String sql = "select * from " + DBHelper.MINELOGBIN_TABLE;
            Cursor cursor = db.rawQuery(sql,null);
            String[] strArray = {"id","logid","title","time"};
            while(cursor.moveToNext()){
                Map<String, String> map = new HashMap<>();
                for (int i = 0; i < strArray.length; i++){
                    map.put(strArray[i], cursor.getString(i));
                }
                list.add(map);
            }
            cursor.close();
        }catch (Exception e){
            Log.e(TAG, "selectNoteBin error", e);
        }finally {
            if (db != null) {
                db.close();
            }
        }
        return list;
    }
    /*还原笔记*/
    public boolean restoreReport(int id ,int rpid){
        String sql = "update "+DBHelper.REPORT_TABLE +
                " set isdelete=0 where id=?";
        String sql2 = " delete from " + DBHelper.REPORTBIN_TABLE + " where id=? ";
        try {
            db = dbHelper.getWritableDatabase();
            db.execSQL(sql,new Object[]{rpid});
            db.execSQL(sql2,new Object[]{id});
            return true;
        }catch (Exception e) {
            Log.e(TAG, "restoreNote", e);
            return false;
        } finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*还原笔记*/
    public boolean restoreLog(int id ,int logid){
        String sql = "update "+DBHelper.MINELOG_TABLE+
                " set isdelete=0 where id=?";
        String sql2 = " delete from " + DBHelper.MINELOGBIN_TABLE + " where id=? ";
        try {
            db = dbHelper.getWritableDatabase();
            db.execSQL(sql,new Object[]{logid});
            db.execSQL(sql2,new Object[]{id});
            return true;
        }catch (Exception e) {
            Log.e(TAG, "restoreNote", e);
            return false;
        } finally {
            if (db != null) {
                db.close();
            }
        }
    }

    /*设置标语*/
    public boolean editSlogan(String slogan){
        String sql = "update "+DBHelper.USER_TABLE+ " set slogan=?";
        try {
            db = dbHelper.getWritableDatabase();
            db.execSQL(sql,new Object[]{slogan});
            return true;
        }catch (Exception e) {
            Log.e(TAG, "editSlogan", e);
            return false;
        } finally {
            if (db != null) {
                db.close();
            }
        }
    }

    // 设置备注
    public boolean addMark(String note,int id){
        String sql = "update "+DBHelper.PLANTASK_TABLE+ " set note=? where planid=?";
        try {
            db = dbHelper.getWritableDatabase();
            db.execSQL(sql,new Object[]{note,id});
            return true;
        }catch (Exception e) {
            Log.e(TAG, "addMark", e);
            return false;
        } finally {
            if (db != null) {
                db.close();
            }
        }
    }

    public byte[] readResource(InputStream inputStream){
        try {
            ByteArrayOutputStream outputStream=new ByteArrayOutputStream();
            byte [] buffer=new byte[1024];
            int len;
            while( (len=inputStream.read(buffer))!=-1){
                outputStream.write(buffer,0,len);
            }
            inputStream.close();
            outputStream.close();
            return outputStream.toByteArray();
        } catch (Exception e) {
            Log.e(TAG, "readResource error", e);
        }
        return null;
    }
}
